-- For a given registration, returns whether VAT should be charged
-- @eventCode = The code of the event being registered for
-- @registrantId = The ID of the contact who is registering
-- @registrantCountry = The country the contact is registering from, either the country name or country code can be used
-- @applyVat OUT = Returns to a bit to indicate whether VAT should or should not be charged in this registration scenario
-- The procedure also returns single row single column resultset containing the value returned by @applyVat OUT

CREATE PROCEDURE [dbo].[asi_IsVatTaxableRegistration]
    @eventCode varchar(10),
    @registrantId varchar(10),
    @registrantCountry varchar(25),
    @applyVat bit = NULL OUT
AS
BEGIN

    -- declare variables    
    DECLARE @isVatRegistered bit
    DECLARE @isVatExempt bit
    DECLARE @isCompany bit
    DECLARE @registrantVatCountry varchar(25)
     
    -- default the return value to true
    -- this effectively means, handle tax as you always did
    -- if we fail any validations we want the system to behave as it always did
    SET @applyVat = 1

    -- verify the contact exists, and retrieve some basic details
    -- USE_VAT_TAXATION is confusing, if a contact is marked VAT Exempt, [USE_VAT_TAXATION] is actually 1
    IF LEN(ISNULL(@registrantId, ''))=0
    BEGIN
        GOTO ReturnValue
    END
    SELECT @isCompany = n.[COMPANY_RECORD],
           @isVatRegistered = CASE WHEN LEN(ISNULL(nf.[VAT_REG_NUMBER], ''))>0 THEN 1 ELSE 0 END,
           @isVatExempt = CASE WHEN ISNULL(nf.[USE_VAT_TAXATION], 0)=1 THEN 1 ELSE 0 END,
           @registrantVatCountry = ISNULL(nf.[VAT_COUNTRY],'')
      FROM [dbo].[Name] n
           LEFT OUTER JOIN [dbo].[Name_Fin] nf ON n.[ID] = nf.[ID]
     WHERE n.[ID]=@registrantId
    IF @@ROWCOUNT=0
    BEGIN
        GOTO ReturnValue
    END

    -- if the contact is vat exempt, go no further
    IF @isVatExempt=1
    BEGIN
        SET @applyVat = 0
        GOTO ReturnValue
    END
        
    -- see if parent record is a company record (if there is a parent)
    IF @isCompany=0
    BEGIN
        SELECT @isCompany = p.[COMPANY_RECORD]
          FROM [dbo].[Name] p
               LEFT OUTER JOIN [dbo].[Name] c ON p.[ID] = c.[CO_ID]
         WHERE c.[ID]=@registrantId
    END
    
    EXEC [dbo].[asi_IsVatTaxableRegistration2] @eventCode, @registrantCountry, @isCompany, @isVatRegistered, @registrantVatCountry, @applyVat OUT
ReturnValue:    
    
    -- output parameter has already been set
    -- also return the value as a resultset for desktop
    SELECT @applyVat AS [ApplyVat]
    
END


GO
GRANT EXECUTE ON  [dbo].[asi_IsVatTaxableRegistration] TO [IMIS]
GO